

/**
 * mysql 插入
 * @param tabel 表名
 * @param install_val   字段名
 * @param params    字段内容
 */
function tabel_install(connection,tabel,install_val,params) {
    return new Promise((resolve, reject) => {
        //sql语句
        const sql = `insert into ${tabel} (` + install_val.join(',') + `) values (` + params.map(val => typeof val === 'string' ? `'${val}'` : val).join(',') + ` );`;
        // var id = 0
        connection.query(sql, (err, result) => {
            if (err) {
                reject(null); // 将错误作为第一个参数传递给回调函数
            } else {
                resolve(result.insertId); // 将插入的 id 作为第二个参数传递给回调函数
            }
        });
    });
}

/**
 * 修改
 * @param tabel
 * @param update_data
 * @param where
 * @returns {Promise<unknown>}
 */
function mysql_update(connection,tabel,update_data,where){
    return new Promise((resolve, reject) => {
        //组装查询语句
        var sql = `UPDATE ${tabel} set `+set_sql(update_data);
        if(where){
            sql += ` where `+where_sql(where)
        }
        sql += `;`;

        connection.query(sql, (err, result) => {

            if (err) {
                reject(err); // 表示发生错误
            } else {
                resolve(result);
            }
            // // 在所有操作完成后关闭连接
            // connection.end();
        });
    });
}

function mysql_select(connection,tabel,where){
    return new Promise((resolve, reject) => {

        var sql = `select * from ${tabel} `;
        if(where){
            sql += ` where `+where_sql(where)
        }
        sql += `;`;

        connection.query(sql, (err, result) => {
           if(err){
               resolve([])
           }else{
               resolve(result)
           }
        });

    });
}



/**
 * 向数组生成为修改字符串(键名=键值,键名=键值)
 * @param {*} data 
 * @returns 
 */
function set_sql(data){
    const keyValuePairs = [];

    for (const key in data) {
        if (data.hasOwnProperty(key)) {
            const value = data[key];
            if (typeof value === 'string') {
                keyValuePairs.push(`${key} = '${value}'`);
            } else {
                keyValuePairs.push(`${key} = ${value}`);
            }
        }
    }

    const result = keyValuePairs.join(', ');
    return result;
}

/**
 * 快速生成为 where
 * @param {*} data 
 * @returns 
 */
function where_sql(data){
    const keyValuePairs = [];

    for (const key in data) {
        if (data.hasOwnProperty(key)) {
            const value = data[key];
            if (typeof value === 'string') {
                keyValuePairs.push(`${key} = '${value}'`);
            } else if(Object.prototype.toString.call(value) === '[object Array]'){

                switch (value[0]){
                    case 'in':
                        keyValuePairs.push(`${key} ${value[0]} (${value[1]})`);
                        break;
                    case '<':
                        keyValuePairs.push(`${key} ${value[0]} ${value[1]}`);
                        break;
                    case '>':
                        keyValuePairs.push(`${key} ${value[0]} ${value[1]}`);
                        break;
                    case '<=':
                        keyValuePairs.push(`${key} ${value[0]} ${value[1]}`);
                        break;
                    case '>=':
                        keyValuePairs.push(`${key} ${value[0]} ${value[1]}`);
                        break;
                    default:
                        keyValuePairs.push(`${key} ${value[0]}(${value[1]})`);
                        break;
                }

            }else{
                keyValuePairs.push(`${key} = ${value}`);
            }
        }
    }

    const result = keyValuePairs.join(' and ');
    return result;
}

module.exports = {
    tabel_install,mysql_update,mysql_select
};